Version: 8.6

Configuring AWS and Qrvey for Redshift Connections

The steps below detail how to configure Qrvey to access Redshift clusters for the purpose of loading data into Qrvey for data analysis.

The changes allow Qrvey to access the cluster and also enable the cluster to export data to S3 for access by Qrvey.

Steps

Create a new Secret that points to the Redshift cluster.

a. Open the AWS console.
b. Use the Redshift AWS account.
c. Open AWS Secrets Manager.
d. Create a new Secret.
e. Select Credentials for a Redshift cluster.
f. Specify the User name and Password for the Redshift cluster.
g. Select the DB Cluster.
h. Select Next.
i. Enter a Secret name. For example: RedshiftClusterSecret.
j. Select Next.
k. Optionally configure automatic rotation, if desired.
l. Select Next.
m. Select Store.
n. Note this Secret’s ARN string for later use.

Alternatively, if using Redshift in serverless mode, then:

a. Open the AWS console.
b. Use the Redshift AWS account.
c. Open AWS Secrets Manager.
d. Create a new Secret.
e. Select Other type of secret.
f. Specify Key/value pairs. This is more easily done using the Plaintext feature. Here is an example:

{
"username":"MY_USERNAME",
"password":"MY_PASSWORD",
"engine":"redshift",
"host":"default.MY_ACCOUNT.us-east-1.redshift-serverless.amazonaws.com",
"port":5439,
"workgroupName":"default"
}

g. Select Next.
h. Select Store.
i. Note this Secret’s ARN string for later use.

Set permissions for the Qrvey Database lambda.

a. Use the Qrvey AWS account.
b. Open AWS Identity and Access Management (IAM).
c. Select Roles.
d. Search for the DB lambda role. It contains the strings DBDatasourcePumpFunction, elastic-view-function-role, and <prefix>TaskExecutionRole. There is one Role for each Qrvey deployment. Select the role applying to the current Qrvey deployment. Note down the ARN to be used later as ROLE_DB_DATASOURCE_PUMP_FUNCTION, ELASTIC_VIEW_FUNCTION_ROLE, and ECS_TASK_EXECUTION_ROLE
e. Select Add inline policy.
f. Select the JSON tab.
g. Paste the policy, replacing AWS_ACCOUNT_REDSHIFT with the AWS account number for the Redshift cluster.

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": [
            "arn:aws:iam::AWS_ACCOUNT_REDSHIFT:role/RedshiftDataAccessRole"
        ]
    }
}

h. Select Review policy.
i. Name the policy. Example: RedshiftDataAccessAssumeRole
j. Select Create Policy. See that the new policy has been added.

Add a trust relationship for Qrvey role DbDatasourceExportToS3Role. (During data loads, Qrvey will temporarily move the data to S3.)

a. Continue in the Qrvey AWS account, IAM.
b. Search for the export role. It contains this string: DbDatasourceExportToS3Role.
c. Note the ARN of role DbDatasourceExportToS3Role, to be used later.
d. Select the Trust relationships tab.
e. Select Edit trust relationship.
f. Paste the trust relationship below, replacing the appropriate ARN.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::AWS_ACCOUNT_REDSHIFT:role/RedshiftDataAccessRole"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

g. Save the trust relationship.

Create a new Policy.

a. Switch the Redshift AWS account.
b. Open AWS Identity and Access Management (IAM)
c. Select Policies.
d. Select Create policy.
e. Select the JSON tab.
f. Paste the policy below below, replacing the appropriate ARNs.

i. Set the Secret ARN generated above, replacing SECRET_ARN.
ii. Set the Role ARN for the DbDatasourceExportToS3Role, noted above, replacing EXPORT_TO_S3_ARN.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift-data:CancelStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:ExecuteStatement"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "SECRET_ARN"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole"
            ],
            "Resource": [
                "EXPORT_TO_S3_ARN"
            ]
        }
    ]
}

g. Select Next: Tags.
h. Select Next: Review.
i. Name the new Policy. For example: “RedshiftDataAccessPolicy”.

Create a new Role

a. Continue in the Redshift AWS account, IAM.
b. Select Roles.
c. Select Create Role.
d. Select service Redshift.
e. Select Redshift - Customizable.
f. Select Next: Permissions.
g. Use the filter to find the RedshiftDataAccessPolicy created above.
h. Check its box.
i. Select Next: Tags.
j. Select Next: Review.
k. Enter the Role name. For example: RedshiftDataAccessRole.
l. Select Create Role.
m. Note this Role’s ARN string for later use.

Add a Trust Relation to the new Role.

a. Search for the lambda roles that contain in the name DBDatasourcePumpFunction and elastic-view-function-role. There is one Role for each Qrvey deployment. Select the role applying to the current Qrvey deployment.
b. Select Roles.
c. Locate the newly created Role.
d. Select the Trust Relations tab.
e. Select Edit trust relationship.
f. Select the tab Trust relationships, then Edit trust relationship.
g. Paste the trust relationship shown below, replacing ROLE_DB_DATASOURCE_PUMP_FUNCTION, ELASTIC_VIEW_FUNCTION_ROLE, and ECS_TASK_EXECUTION_ROLE with the Roles noted in Step 2.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ROLE_DB_DATASOURCE_PUMP_FUNCTION"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ELASTIC_VIEW_FUNCTION_ROLE"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "ECS_TASK_EXECUTION_ROLE"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

h. Select Update Trust Policy.

Associate the Redshift cluster with the new Role.

a. Go to AWS Redshift.
b. Select the desired cluster.
c. Select the Properties tab.
d. Select the Manage IAM roles button.
e. Select Enter ARN.
f. Enter the ARN of the newly created Role.
g. Select Associate IAM Role.
h. Select Save changes.

Open the Qrvey application.

a. Select Datasets.
b. Select Connections.
c. Create a new Connection for Redshift.
d. Enter the Secret ARN and Role ARN created above.
e. Test the new Connection.

Debugging

Connection Test timeout error:

This is an error with the Postgres driver trying to connect to Redshift during the Connection Test.

If Qrvey is in a different account, then a VPC is necessary for DBDatasourcePump

Not authorized to assume IAM Role

Error in Redshift SQL UNLOAD command: ERROR: User 
arn:aws:redshift:us-east-1:790133296469:dbuser:redshift-drdev/awsuser is not authorized to assume IAM 
Role arn:aws:iam::790133296469:role/RedshiftDataAccessRole,arn:aws:iam::790133296469:role/
rouup_dataload_DbDatasourceExportToS3Role.

This error occurs when the trust relationship for the role DbDatasourceExportToS3Role is not set. See step 3 above.

Steps​

Debugging​

Connection Test timeout error:​

Not authorized to assume IAM Role​

Steps

Debugging

Connection Test timeout error:

Not authorized to assume IAM Role